using System.Data;
using System.Data.SqlClient;
using Framework.Library;
using Framework.Library.Utilities;
using System.Web;

namespace Framework.Module.Logging.Lib
{
    public class LogDB
    {
        public static bool UpdateLog(string counterKey, int counterValue, int counterID)
        {
            int portalid = ConvertUtility.ToInt32(HttpContext.Current.Request.QueryString["portalid"]);
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("Loging_UpdateLog", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@CounterKey", counterKey);
            dbCmd.Parameters.AddWithValue("@CounterValue", counterValue);
            dbCmd.Parameters.AddWithValue("@CounterID", counterID);
            dbCmd.Parameters.AddWithValue("@PortalID", portalid);
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
                return true;
            }
            finally
            {
                dbConn.Close();
            }
        }

        public static DataTable GetLogNames()
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand();
            dbCmd.Connection = dbConn;

            dbCmd.CommandType = CommandType.Text;
            dbCmd.CommandText = "SELECT DISTINCT CounterKey FROM Loging_Logs where PortalID = " + AppEnv.PortalId() + " order by CounterKey";

            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);

            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public static DataTable GetLogValues(string keyName, string startTime, string endTime)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand();
            dbCmd.Connection = dbConn;

            dbCmd.CommandType = CommandType.Text;
            dbCmd.CommandText = "SELECT CounterKey,Today, CounterValue, CounterID FROM Loging_Logs" +
                " WHERE CounterKey = '" + keyName + "'" +
                " AND today >='" + startTime + "'" +
                " AND today<='" + endTime + "' and  PortalID = " + AppEnv.PortalId() + " ORDER BY ToDay ASC";

            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public static DataTable GetLogValuesbyDate(string time, string colections)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand();
            dbCmd.Connection = dbConn;

            dbCmd.CommandType = CommandType.Text;
            dbCmd.CommandText = "SELECT CounterKey,Today, CounterValue, CounterID FROM Loging_Logs" +
                " WHERE today ='" + time + "'" +                
                " AND PATINDEX('%|' + CAST(CounterID as varchar) + '|%','|" + colections + "') >0 " +
                " AND  PortalID = " + AppEnv.PortalId() +
                " ORDER BY CounterID ASC";
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public static DataTable GetDateLog(string keyName, string startTime, string endTime, string colections)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand();
            dbCmd.Connection = dbConn;

            dbCmd.CommandType = CommandType.Text;
            dbCmd.CommandText = "SELECT Today FROM Loging_Logs" +
                " WHERE CounterKey = '" + keyName + "'" +
                " AND today >='" + startTime + "'" +
                " AND today<='" + endTime + "'" +
                " AND PATINDEX('%|' + CAST(CounterID as varchar) + '|%','|" + colections + "') >0 " +
                " AND  PortalID = " + AppEnv.PortalId() +
                " Group by Today ORDER BY ToDay ASC";
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
    }
}
